/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package cafe.Model;

import cafe.DataObject.Catalog;
import cafe.DataObject.Customer;
import cafe.DataObject.DataObject;
import cafe.Model.Utility.DBConnection;
import cafe.Model.Utility.DBConnectionPool;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author HoangLong
 */
public class CatalogModel extends AbstractModel {
    private static CatalogModel instance = null;
    
    private DBConnectionPool connectionPool = null;
    
    /**
     * Get all table and it's informations
     *
     * @return ArrayList<Table> all table in database
     */
    private CatalogModel() {
        this.connectionPool = DBConnectionPool.getInstance();
    }
    
    public static synchronized CatalogModel getInstance() {
        if (instance == null) {
            instance = new CatalogModel();
        }
        
        return instance;
    }
    
    @Override
    public List<? extends DataObject> getAll() {
        
        List<Catalog> result = new LinkedList();
        
        // Connect DB
        DBConnection connectionObject = null;
        
        try {
            connectionObject = this.connectionPool.getConnection();
        }
        catch (Exception e) {
            
        }
        
        //NOTE: we must use ` ` to cover table name 
        String query = "SELECT * FROM `catalog` ";

        ResultSet rs = null;
        try {
            rs = connectionObject.query(query);

            while (rs.next()) {
                Catalog catalog = new Catalog();
                catalog.setId(rs.getString("id"));
                catalog.setName(rs.getString("name"));

                result.add(catalog);
            }

        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

            try {
                if (null != rs) {
                    rs.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(CustomerModel.class.getName()).log(Level.SEVERE, null, ex);
            }
            return result;
        }
    }
    
    @Override
    public Catalog getByID(String id) {
        Catalog catalog = null;

        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "SELECT * FROM `catalog` WHERE `id` = ? LIMIT 1;";

        ResultSet rs = null;
        try {
            rs = connectionObject.query(query, id);

            while (rs.next()) {
                catalog = new Catalog();
                catalog.setId(rs.getString("id"));
                catalog.setName(rs.getString("name"));                
            }

        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

            try {
                if (null != rs) {
                    rs.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(CatalogModel.class.getName()).log(Level.SEVERE, null, ex);
            }
            
            return catalog;
        }
    }

    @Override
    public int insert(DataObject record) {
        Catalog catalog = (Catalog) record;
        
        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "INSERT INTO `catalog`(`name`) " +
                       "VALUES (?)";

        int result = 0;
        
        try {
            connectionObject.excute(query, catalog.toObjectArray());

         
        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

           
            return result;
        }
    }

    @Override
    public int edit(String fromRecordId, DataObject toRecord) {
        Catalog catalog = (Catalog) toRecord;
        
        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "UPDATE `catalog` "
                + "SET `name` = ?"
                + "WHERE `id` = ?";

        int result = 0;
        
        try {
            connectionObject.excute(query, catalog.getName(), fromRecordId);

         
        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

           
            return result;
        }
    }

    @Override
    public int delete(String id) {
        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "DELETE FROM `catalog` WHERE `id` = ?;";

        int result = 0;
        
        try {
            connectionObject.excute(query, id);

         
        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

           
            return result;
        }
    }
}
